********************************************************************************
* This dofile load and process agricultural data from NASS and FSA
********************************************************************************

clear all
cd "$root"

**# Import and calculate the county-level ratio of irrigation land in all croplant in year 1997
* Total Cropland
import delimited "data/raw/NASS/cropland_harvested.csv", clear 
rename value total_cropland
gen fips = stateansi*1000 + countyansi
destring total_cropland, force replace ignore(",")
keep year total_cropland fips 
drop if missing(fips)
save data/temp/total_cropland.dta, replace

* Irrigated Cropland
import delimited "data/raw/NASS/cropland_harvested_irrigated.csv", clear 
rename value irrigated_cropland
gen fips = stateansi*1000 + countyansi
destring irrigated_cropland, force replace ignore(",")
keep year irrigated_cropland fips 
drop if missing(fips)
save data/temp/irrigated_cropland.dta, replace

merge 1:1 fips year using data/temp/total_cropland.dta, nogen

replace irrigated_cropland = 0 if missing( irrigated_cropland )
keep if year == 1997
gen irrigated_ratio = irrigated_cropland/total_cropland

save data/temp/irrigated_ratio.dta,replace

* Planted Ratio, Corn
forvalues yr = 2021(1)2023 {
	import excel "data\raw\FSA/`yr'_fsa_acres_web.xlsx", sheet("county_data") firstrow clear
	keep if crop_name == "CORN"
	keep if crop_intended_use_descr == "Grain"
	rename state_county_code fips
	ren (Planted_Acres Prevented_Acres Failded_Acres Volunteer_Acres Not_Planted_Acres) (corn_planted_fsa corn_prevented_fsa corn_failed_fsa corn_volunteered_fsa corn_nplanted_fsa)

	keep fips *fsa
	collapse (rawsum) *fsa, by(fips)
	gen year = `yr'
	replace corn_prevented_fsa = 0 if corn_prevented_fsa == .
	gen corn_plratio = (corn_planted_fsa + corn_failed_fsa) / (corn_planted_fsa + corn_prevented_fsa + corn_failed_fsa) 
	keep year fips *fsa *ratio
	save data/temp/planted_ratio_corn`yr'.dta, replace
}
append using data/temp/planted_ratio_corn2022.dta
append using data/temp/planted_ratio_corn2021.dta
save data/temp/planted_ratio_corn2123.dta, replace

* Planted Ratio, Soybeans
forvalues yr = 2021(1)2023 {
	import excel "data\raw\FSA/`yr'_fsa_acres_web.xlsx", sheet("county_data") firstrow clear
	keep if crop_name == "SOYBEANS"
	keep if crop_intended_use_descr == "Grain"
	rename state_county_code fips
	ren (Planted_Acres Prevented_Acres Failded_Acres Volunteer_Acres Not_Planted_Acres) (soybeans_planted_fsa soybeans_prevented_fsa soybeans_failed_fsa soybeans_volunteered_fsa soybeans_nplanted_fsa)

	keep fips *fsa
	collapse (rawsum) *fsa, by(fips)
	gen year = `yr'
	replace soybeans_prevented_fsa = 0 if soybeans_prevented_fsa == .
	gen soybeans_plratio = (soybeans_planted_fsa + soybeans_failed_fsa) / (soybeans_planted_fsa + soybeans_prevented_fsa + soybeans_failed_fsa) 
	keep year fips *fsa *ratio
	save data/temp/planted_ratio_soybeans`yr'.dta, replace
}

append using data/temp/planted_ratio_soybeans2022.dta
append using data/temp/planted_ratio_soybeans2021.dta
save data/temp/planted_ratio_soybeans2123.dta, replace

**# Import county-level FSA prevented acres data from Boyer et al. calculated the ratio of planted acres
foreach var in corn soybeans {
	import excel using "data\raw\AEPP\Prevented planting acres 96-2020.xlsx", sheet("`var'") firstrow clear

	rename Year year
	drop if year < 1999
	destring, force replace

	gen fips= st_fips*1000 + ct_fips
	drop if missing(fips)
	
	collapse (rawsum) `var'_planted_fsa `var'_prevented_fsa `var'_failed_fsa `var'_volunteered_fsa `var'_nplanted_fsa `var'_pplanted_fsa, by(fips year)
	replace `var'_prevented_fsa = 0 if `var'_prevented_fsa == .

	//This is the definition of planting ratio
	gen `var'_plratio = (`var'_planted_fsa +`var'_failed_fsa) / ///
	(`var'_planted_fsa + `var'_prevented_fsa + `var'_failed_fsa) 
	
	append using data/temp/planted_ratio_`var'2123.dta
	save data/temp/planted_ratio_`var'.dta, replace
}

**# Import and clean county-level NASS data 
local crops "corn soybeans"  
foreach crop of local crops {
    local vars "harvested planted yield production"
	
	foreach var of local vars {
		import delimited data/raw/NASS/`crop'_`var'.csv, clear
		keep if program == "SURVEY" & domain == "TOTAL"
		duplicates list
		gen fips = stateansi*1000 + countyansi
		rename value `crop'_`var'
		//replace fips= stateansi if missing(fips)
		drop if missing(fips)
		duplicates tag fips year, gen(temp)
        drop if temp > 0 & missing(cv)
		drop temp
		keep year `crop'_`var' fips stateansi
		destring `crop'_`var', ignore(",") force replace
		save data/temp/`crop'_`var'.dta, replace
		keep if year == 2000
		ren `crop'_`var' `crop'_`var'00
		save data/temp/`crop'_`var'00.dta, replace
	}
} 

**# Merging all county-level data
local crops "corn soybeans"  
foreach crop of local crops {
    local vars "harvested planted yield production"
	foreach var of local vars {
		merge 1:1 fips year using data/temp/`crop'_`var'.dta, nogen
		merge m:1 fips using data/temp/`crop'_`var'00.dta, nogen
	}
} 

merge m:1 fips using data/temp/irrigated_ratio.dta, nogen
drop if missing(fips)
merge 1:1 fips year using data/temp/planted_ratio_corn.dta, nogen
merge 1:1 fips year using data/temp/planted_ratio_soybeans.dta, nogen
replace stateansi = trunc(fips/1000) if missing(stateansi)
drop if trunc(fips/1000) == 0 //drop states
save data/temp/crop_merged_county.dta, replace

**# Processing state-level agricultural (NASS) data
local crops "corn soybeans"
foreach crop of local crops {
	   	import delimited data/raw/NASS/`crop'_price.csv, clear
	    keep if period == "MARKETING YEAR"
		rename value `crop'_price
		keep year stateansi `crop'_price
		drop if missing(stateansi)
		save data/temp/`crop'_price.dta, replace	
	}

local crops "corn soybeans"
foreach crop of local crops { 
		merge 1:1 stateansi year using data/temp/`crop'_price.dta, nogen
} 
destring corn* soybean*, ignore(",") force replace
save data/temp/crop_merged_state.dta, replace
merge 1:m stateansi year using data/temp/crop_merged_county.dta, nogen

save "data\temp\ag_data.dta", replace

capture {
	erase data/temp/total_cropland.dta
	erase data/temp/irrigated_cropland.dta
	erase data/temp/irrigated_ratio.dta
	erase data/temp/planted_ratio_corn2123.dta
	erase data/temp/planted_ratio_soybeans2123.dta
	erase data/temp/crop_merged_county.dta
	erase data/temp/crop_merged_state.dta
}
	
local crops "corn soybeans"
foreach crop of local crops {
	capture {
		erase data/temp/`crop'_price_month.dta
		erase data/temp/planted_ratio_`crop'.dta
	}
}

local crops "corn soybeans"
local vars "harvested planted yield production stock price" 
foreach crop of local crops {
	foreach var of local vars {
		capture	erase data/temp/`crop'_`var'.dta
	}
}

local crops "corn soybeans"
local vars "harvested planted yield production" 
foreach crop of local crops {
	foreach var of local vars {
		capture	erase data/temp/`crop'_`var'00.dta
	}
}

forvalues yr = 2021(1)2023 {
	capture {
		erase data/temp/planted_ratio_corn`yr'.dta
		erase data/temp/planted_ratio_soybeans`yr'.dta
	}
}